<!DOCTYPE html>
<html>
<head>
    <title>子查询[未整理]</title>

    <meta charset="UTF-8"/>

    <meta content="text/html; charset=utf-8" http-equiv="Content-Type"/>

    <meta name="HandheldFriendly" content="true"/>

    <meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no"/>

    <meta name="apple-mobile-web-app-capable" content="yes"/>

    <meta name="apple-mobile-web-app-status-bar-style" content="black"/>

    <link href="./asset/farbox/basic.css" type="text/css" rel="stylesheet"/>
    <link href="./asset/farbox/main.css" type="text/css" rel="stylesheet"/>

    <style type="text/css">
        
            span.md_line{margin-bottom:0.5em; display:block; line-height:1.89}
            .md_line br{ display: none;}
            
    </style>

    <script>
        var flowchat_options = {
            'x': 0, 'y': 0, 'line-width': 1, 'line-length': 50, 'text-margin': 10, 'font-size': 13,
            'font-color': '#3c3c3c', 'line-color': '#666666', 'element-color': '#666666', 'fill': 'transparent',
            'yes-text': 'yes', 'no-text': 'no', 'arrow-end': 'block', 'class': 'flowchart', 'scale': 1,
            'symbols': { 'start': {}, 'end': {}, 'condition': {}, 'inputoutput': {}, 'operation': {}, 'subroutine': {}}
        }
    </script>
    <script type="text/javascript" src="./asset/other/raphael-min.js"></script>
    <script type="text/javascript" src="./asset/other/flowchart.js"></script>
    <script type="text/javascript" src="./asset/other/echarts.min.js"></script>


</head>

<body>
    
        <a href="./index.html" id="go_to_home"> &lt; home </a>
    
    <div id="sidebar">
        <ul id="sidebar_body">
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./001.软件下载安装配置.html" class="">第一章 软件下载安装配置</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./002.数据库操作.html" class="">002.数据库操作</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./003.数据表操作.html" class="">003.数据表操作</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./004.PHP与sql server连接.html" class="">004.PHP与sql server连接</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./005.数据的增删改.html" class="">005.数据的增删改</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./006.数据操作-简单查询.html" class="">006.数据操作-简单查询</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./T-SQL[未整理].html" class="">第三章:T-SQL</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./事务-索引-视图[未整理].html" class="">事务-索引-视图[未整理]</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./子查询[未整理].html" class="selected">子查询[未整理]</a>
                </li>
            

            
        
            
            
            
            

            
                <li class="level_1 file">
                    <a href="./存储过程[未整理].html" class="">存储过程[未整理]</a>
                </li>
            

            
        
        </ul>
    </div>

    <div id="main">
        <div class="content_body">
            <h1 class="title"> 子查询[未整理] </h1>
            <div class="doc_post"> <h1 id="toc_0">第四章笔记</h1>

<h2 id="toc_1">回顾查询</h2>

<p class="md_block">
    <span class="md_line">基本语法</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">--基本查询 查询某表所有列</span>
<span class="k">select</span> <span class="o">*</span> <span class="k">from</span> <span class="err">表名</span>
<span class="c1">--基本查询 查询某表选择列</span>
<span class="k">select</span> <span class="err">列名</span><span class="p">[,</span><span class="err">列名</span><span class="p">,</span><span class="err">列名</span><span class="p">,...]</span> <span class="k">from</span> <span class="err">表名</span><span class="c1">-- 中括号中内容可选</span>
<span class="c1">--基本询询 给列起别名</span>
<span class="k">select</span> <span class="err">列名</span> <span class="k">as</span> <span class="err">别名</span> <span class="k">from</span> <span class="err">表名</span> <span class="c1">--用as的标准写法</span>
<span class="k">select</span> <span class="err">列名</span> <span class="err">别名</span> <span class="k">from</span> <span class="err">表名</span><span class="c1">--用as的简写</span>
<span class="k">select</span> <span class="err">别名</span> <span class="o">=</span><span class="err">列名</span> <span class="k">from</span> <span class="err">表名</span> <span class="c1">--用=符号的写法</span>
<span class="c1">--基本查询 带条件过滤(where)</span>
<span class="k">select</span> <span class="o">*</span> <span class="k">from</span> <span class="err">表名</span> <span class="k">where</span> <span class="err">条件</span>
<span class="c1">--如果有多个条件,则用 and 或 or 连接进行逻辑运算</span>
<span class="k">select</span> <span class="o">*</span> <span class="k">from</span> <span class="err">表名</span> <span class="k">where</span> <span class="err">条件</span><span class="mi">1</span> <span class="k">and</span> <span class="err">条件</span><span class="mi">2</span> <span class="k">or</span> <span class="err">条件</span><span class="mi">3</span>
<span class="c1">--回顾查询条件</span>
<span class="c1">--单条件 年龄大于14岁</span>
    <span class="k">where</span> <span class="err">年龄列名</span> <span class="o">&gt;</span> <span class="mi">14</span>
    <span class="c1">--多条件 年龄大于14岁与性别是男生</span>
    <span class="k">where</span> <span class="err">年龄列名</span> <span class="o">&gt;</span> <span class="mi">14</span> <span class="k">and</span> <span class="err">性别列</span> <span class="o">=</span><span class="s1">&#39;男&#39;</span>
    <span class="c1">--多条件between比如:查年龄在14-20岁之间(包含14与20)</span>
    <span class="k">where</span>  <span class="err">年龄列</span> <span class="k">between</span> <span class="mi">14</span> <span class="k">and</span> <span class="mi">20</span>
    <span class="c1">--等同于</span>
    <span class="k">where</span> <span class="err">年龄列</span> <span class="o">&gt;=</span> <span class="mi">14</span> <span class="k">and</span> <span class="err">年龄列</span> <span class="o">&lt;=</span><span class="mi">20</span>
<span class="c1">--查询条件,查询空(空一般在数据库有两种 null 与 &#39;&#39;(空字符串))</span>
    <span class="k">where</span> <span class="err">列名</span> <span class="k">is</span> <span class="k">null</span> <span class="c1">--查询空</span>
    <span class="k">where</span> <span class="err">列名</span> <span class="k">is</span> <span class="k">not</span> <span class="k">null</span> <span class="c1">--查询非空</span>
    <span class="k">where</span> <span class="err">列名</span> <span class="o">=</span> <span class="s1">&#39;&#39;</span><span class="c1">--查询空字符串</span>
    <span class="k">where</span> <span class="err">列名</span> <span class="o">&lt;&gt;</span> <span class="s1">&#39;&#39;</span> <span class="c1">--查询非空字符串</span>
<span class="c1">--查询条件 in(某个范围值的查值,枚举)</span>
    <span class="k">where</span> <span class="err">列名</span> <span class="k">in</span><span class="p">(</span><span class="mi">15</span><span class="p">,</span><span class="mi">20</span><span class="p">,</span><span class="mi">23</span><span class="p">,</span><span class="mi">16</span><span class="p">)</span>
<span class="c1">--模糊查询 like (结合: % _ [] 通配符使用)</span>
    <span class="k">where</span> <span class="err">名字列</span> <span class="k">like</span> <span class="s1">&#39;李%&#39;</span> <span class="c1">--模糊查询全部姓李的</span>
    <span class="k">where</span> <span class="err">名字列</span> <span class="k">like</span> <span class="s1">&#39;%帅%&#39;</span> <span class="c1">-- 名字中带帅字的</span>
    <span class="k">where</span> <span class="err">名字列</span> <span class="k">like</span> <span class="s1">&#39;李_&#39;</span> <span class="c1">--查询李开头两个字的</span>
    <span class="c1">--[]可以做两件事,转义%与_ 还有个功能:进行枚举.</span>
    <span class="k">where</span> <span class="err">名字列</span> <span class="k">like</span> <span class="s1">&#39;[刘邓王]帅&#39;</span><span class="c1">--查询刘帅或邓帅或王帅.</span>
<span class="c1">--返回限制行数 top  percent</span>
    <span class="k">select</span> <span class="n">top</span> <span class="mi">10</span> <span class="o">*</span> <span class="k">from</span>  <span class="err">表名</span> <span class="c1">--只显示前10行</span>
    <span class="k">select</span> <span class="n">percent</span> <span class="n">top</span> <span class="mi">10</span> <span class="o">*</span> <span class="k">from</span>  <span class="err">表名</span> <span class="c1">--显示总行数的前10%</span>
<span class="c1">--查询结果排序 order by ...</span>
    <span class="c1">--写在最后: order by 列名 规则,列名 规则 例:</span>
        <span class="k">order</span> <span class="k">by</span> <span class="err">列</span><span class="mi">1</span> <span class="k">desc</span><span class="p">,</span><span class="err">列</span><span class="mi">2</span> <span class="c1">--如果不写序序规则,则默认是asc(升序)</span>
<span class="c1">--分组 group by ...  为什么要分组查询? 配合聚合函数.记几个常用聚合函数:sum avg max min count</span>
<span class="k">select</span> <span class="n">groupid</span><span class="p">,</span><span class="k">count</span><span class="p">(</span><span class="n">groupid</span><span class="p">)</span> <span class="k">from</span> <span class="n">st_info</span> <span class="k">group</span> <span class="k">by</span> <span class="n">groupid</span><span class="c1">--查询出每小组的人数</span>
<span class="k">select</span> <span class="n">classid</span><span class="p">,</span><span class="k">count</span><span class="p">(</span><span class="n">classid</span><span class="p">)</span> <span class="k">from</span> <span class="n">st_info</span> <span class="k">group</span> <span class="k">by</span> <span class="n">classid</span> <span class="c1">--查询出每班级的人数</span>
<span class="c1">--对使用了聚合函数的查询结果再进行过滤.</span>
<span class="k">select</span> <span class="n">classid</span><span class="p">,</span><span class="k">count</span><span class="p">(</span><span class="n">classid</span><span class="p">)</span> <span class="k">from</span> <span class="n">st_info</span> <span class="k">group</span> <span class="k">by</span> <span class="n">classid</span>  <span class="k">having</span> <span class="k">count</span><span class="p">(</span><span class="n">classid</span><span class="p">)</span><span class="o">&gt;</span> <span class="mi">50</span><span class="c1">--查询人数大于50的班级.       </span>
</pre></div>

<!--block_code_end-->
<h2 id="toc_2">子查询</h2>

<p class="md_block">
    <span class="md_line">编写T-SQL语句，查看<strong>JAVA基础</strong>成绩比“胡敏”好的学员，要求显示这些学员的信息 ？</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">--分析:先得到胡敏的用户ID,得到胡敏的成绩作为查询条件.</span>
<span class="k">declare</span> <span class="o">@</span><span class="n">uid</span> <span class="nb">int</span>    <span class="c1">--胡敏的用户名</span>
<span class="k">declare</span> <span class="o">@</span><span class="n">subid</span> <span class="nb">int</span> <span class="c1">--JAVA基础的课程ID</span>
<span class="k">declare</span> <span class="o">@</span><span class="n">score</span> <span class="nb">float</span>  <span class="c1">--胡敏JAVA基础的成绩</span>
                      <span class="c1">--</span>
<span class="k">select</span> <span class="o">@</span><span class="n">uid</span> <span class="o">=</span> <span class="n">id</span> <span class="k">from</span> <span class="n">st_info</span> <span class="k">where</span> <span class="n">name</span> <span class="o">=</span> <span class="s1">&#39;胡敏&#39;</span> <span class="c1">--得到胡敏的用户ID</span>
<span class="k">select</span> <span class="o">@</span><span class="n">subid</span> <span class="o">=</span> <span class="n">id</span> <span class="k">from</span> <span class="n">subject</span> <span class="k">where</span> <span class="n">name</span> <span class="o">=</span><span class="s1">&#39;JAVA基础&#39;</span> <span class="c1">--得到JAVA基础的课程ID</span>
                                                       <span class="c1">--</span>
<span class="k">select</span> <span class="o">@</span><span class="n">score</span> <span class="o">=</span> <span class="n">score</span> <span class="k">from</span> <span class="n">st_score</span> <span class="k">where</span> <span class="n">uid</span> <span class="o">=</span> <span class="o">@</span><span class="n">uid</span> <span class="k">and</span> <span class="n">subid</span> <span class="o">=</span> <span class="o">@</span><span class="n">subid</span> <span class="c1">--得到胡敏JAVA基础的成绩</span>
<span class="n">PRINT</span> <span class="o">@</span><span class="n">score</span> <span class="c1">--输出一下胡敏的JAVA基础成绩</span>
<span class="k">select</span> <span class="o">*</span>  <span class="k">from</span> <span class="n">st_score</span> <span class="k">where</span> <span class="n">subid</span> <span class="o">=</span> <span class="o">@</span><span class="n">subid</span> <span class="k">and</span> <span class="n">score</span> <span class="o">&gt;</span> <span class="o">@</span><span class="n">score</span> <span class="c1">--最后结果</span>
<span class="c1">--把上面语句组合成一个语句,就用到子查询</span>
<span class="k">select</span> <span class="o">*</span>  <span class="k">from</span> <span class="n">st_score</span> 
<span class="k">where</span> 
<span class="n">subid</span> <span class="o">=</span> <span class="p">(</span><span class="k">select</span> <span class="n">id</span> <span class="k">from</span> <span class="n">subject</span> <span class="k">where</span> <span class="n">name</span> <span class="o">=</span><span class="s1">&#39;JAVA基础&#39;</span><span class="p">)</span> 
<span class="k">and</span> 
<span class="n">score</span> <span class="o">&gt;</span> <span class="p">(</span><span class="k">select</span> <span class="n">score</span> <span class="k">from</span> <span class="n">st_score</span> <span class="k">where</span> <span class="n">uid</span> <span class="o">=</span> <span class="p">(</span><span class="k">select</span> <span class="n">id</span> <span class="k">from</span> <span class="n">st_info</span> <span class="k">where</span> <span class="n">name</span> <span class="o">=</span> <span class="s1">&#39;胡敏&#39;</span><span class="p">)</span> 
<span class="k">and</span> 
<span class="n">subid</span> <span class="o">=</span> <span class="p">(</span><span class="k">select</span> <span class="n">id</span> <span class="k">from</span> <span class="n">subject</span> <span class="k">where</span> <span class="n">name</span> <span class="o">=</span><span class="s1">&#39;JAVA基础&#39;</span><span class="p">))</span> <span class="c1">--最后结果</span>
</pre></div>

<!--block_code_end-->
<p class="md_block md_has_block_below md_has_block_below_blockquote">
    <span class="md_line">子查询在WHERE语句中的一般用法：</span>
</p>


<blockquote>

<p class="md_block">
    <span class="md_line">SELECT … FROM 表1 WHERE 字段1 &gt;(子查询)<br /></span>
    <span class="md_line">外面的查询称为父查询，括号中嵌入的查询称为子查询 <br /></span>
    <span class="md_line">UPDATE、INSERT、DELETE一起使用，语法类似于SELECT语句 <br /></span>
    <span class="md_line">将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个 </span>
</p>


<h3 id="toc_3">练习</h3>

<p class="md_block">
    <span class="md_line">问题：查询HTML成绩不及格(&lt;=60)的学员。1.用表连接进行查询一次,用子查询写一次(要求显示 UID,SCORE,课程名称)</span>
</p>

</blockquote>
<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="c1">--表连接(join) </span>
<span class="k">select</span> <span class="n">a</span><span class="p">.</span><span class="n">uid</span><span class="p">,</span><span class="n">a</span><span class="p">.</span><span class="n">score</span><span class="p">,</span><span class="n">b</span><span class="p">.</span><span class="n">name</span> <span class="err">课程名称</span> <span class="k">from</span> <span class="n">st_score</span> <span class="n">a</span> <span class="k">join</span> <span class="n">subject</span> <span class="n">b</span> <span class="k">on</span> <span class="n">a</span><span class="p">.</span><span class="n">subid</span> <span class="o">=</span> <span class="n">b</span><span class="p">.</span><span class="n">id</span> <span class="k">where</span> <span class="n">b</span><span class="p">.</span><span class="n">name</span> <span class="o">=</span> <span class="s1">&#39;HTML&#39;</span> <span class="k">AND</span> <span class="n">a</span><span class="p">.</span><span class="n">score</span> <span class="o">&lt;</span> <span class="mi">60</span>
<span class="c1">--子查询</span>
<span class="k">select</span> <span class="n">uid</span><span class="p">,</span><span class="n">score</span><span class="p">,</span><span class="s1">&#39;HTML&#39;</span> <span class="err">课程名称</span> <span class="k">from</span> <span class="n">st_score</span> <span class="k">WHERE</span> <span class="n">score</span> <span class="o">&lt;</span> <span class="mi">60</span> <span class="k">AND</span> <span class="n">subid</span> <span class="o">=</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">id</span> <span class="k">FROM</span> <span class="n">SUBJECT</span> <span class="k">WHERE</span> <span class="n">NAME</span> <span class="o">=</span> <span class="s1">&#39;HTML&#39;</span><span class="p">)</span>
</pre></div>

<!--block_code_end-->
<h2 id="toc_4">in子查询</h2>

<p class="md_block">
    <span class="md_line">问题：查询HTML成绩大于80学员名单(要求返回学生ID,姓名 两列),用in子查询实现。</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">select</span> <span class="n">id</span><span class="p">,</span><span class="n">name</span> <span class="k">from</span> <span class="n">st_info</span> <span class="k">where</span> <span class="n">id</span> <span class="k">in</span><span class="p">(</span><span class="k">select</span> <span class="n">uid</span> <span class="k">from</span> <span class="n">st_score</span> <span class="k">where</span> <span class="n">score</span> <span class="o">&gt;</span> <span class="mi">80</span> <span class="k">and</span> <span class="n">subid</span> <span class="o">=</span> <span class="p">(</span><span class="k">select</span> <span class="n">id</span> <span class="k">from</span> <span class="n">subject</span> <span class="k">where</span> <span class="n">name</span> <span class="o">=</span> <span class="s1">&#39;HTML&#39;</span><span class="p">))</span>
</pre></div>

<!--block_code_end-->
<h1 id="toc_5">exists 子查询</h1>

<p class="md_block">
    <span class="md_line">如果子查询的结果非空，即记录条数1条以上，则EXISTS （子查询）将返回真（true），否则返回假(false) <br /></span>
    <span class="md_line">EXISTS也可以作为WHERE 语句的子查询，但一般都能用IN子查询替换</span>
</p>

<div class="codehilite code_lang_sql  highlight"><pre><span></span><span class="k">select</span> <span class="n">id</span><span class="p">,</span><span class="n">name</span> <span class="k">from</span> <span class="n">st_info</span> <span class="n">A</span>
<span class="k">WHERE</span> <span class="k">EXISTS</span>
<span class="p">(</span>
    <span class="k">SELECT</span> <span class="n">uid</span> <span class="k">FROM</span> <span class="n">st_score</span> <span class="k">where</span> <span class="n">SCORE</span> <span class="o">&gt;</span> <span class="mi">80</span> 
    <span class="k">AND</span> 
    <span class="n">subid</span> <span class="o">=</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">id</span> <span class="k">FROM</span> <span class="n">SUBJECT</span> <span class="k">WHERE</span> <span class="n">NAME</span><span class="o">=</span><span class="s1">&#39;HTML&#39;</span><span class="p">)</span> <span class="k">AND</span> <span class="n">UID</span> <span class="o">=</span> <span class="n">A</span><span class="p">.</span><span class="n">ID</span>
<span class="p">)</span>
</pre></div>

<!--block_code_end-->
<h5 id="toc_6">EXISTS 与 IN做查子查询时的区别:执行效率不同</h5>

<p class="md_block">
    <span class="md_line">--如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：<br /></span>
    <span class="md_line">--A表   B表<br /></span>
    <span class="md_line">select * from A where cc in (select B.cc from B)<br /></span>
    <span class="md_line"> --效率低，用到了A 表上cc 列的索引；<br /></span>
    <span class="md_line">select * from A where exists(select cc from B where B.cc=A.cc)<br /></span>
    <span class="md_line">--如果A表的数据量大(行数多)我们用IN<br /></span>
    <span class="md_line">--如果B表的数据量大(行数多)我们用EXISTS</span>
</p> </div>
        </div>

    </div>





<script>
    // auto focus for menu
    window.onload = function(){
        var sidebar = document.getElementById('sidebar');
        var current = document.getElementsByClassName('selected')[0];
        if (current && sidebar){
            sidebar.scrollTop = current.offsetTop - sidebar.clientHeight/2 + 100
        }
    };
</script>




</body>

</html>